﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.customer.dao.impl;

import java.util.*;

import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.customer.dao.*;
import com.hh.bss.customer.entity.*;
import com.hh.bss.customer.service.*;
import com.hh.bss.customer.vo.query.*;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class CompanyCcDaoImpl extends BaseHibernateDao<CompanyCc,java.lang.Integer>  implements CompanyCcDao<CompanyCc,java.lang.Integer>{

	public Class getEntityClass() {
		return CompanyCc.class;
	}

	public Page findProjectCcCvPage(CompanyCcQuery query) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select concat(ifnull(a.ref_cvid,0),ifnull(a.company_id,0)) id,a.project_id projectId,a.company_id companyId,a.call_dt callDt,'' cvSearchStatus,b1.name companyName,a.ref_cvid cvId,b.cv_name cvName,b.mobile,b.tel phone,b.email,c.deptName deptName,d.titleName titleName,e.current_salary currentSalary,b.sex,b.function,b.level,b.degree,b.work_year workYear,b.expect_location expectLocation from (select project_id,company_id,dept_id,title_id,ref_cvid,max(call_dt) call_dt from company_cc where ref_cvid is not null  group by company_id,dept_id,title_id,project_id,ref_cvid) a ");
		sql.append(" left join  cv b on a.ref_cvid=b.id ");
		sql.append(" left join  company b1 on a.company_id=b1.id ");
		sql.append(" left join (select t1.cv_id,min(t2.name) deptName from company_cv t1,company_dept t2 where t1.dept_id=t2.id group by t1.cv_id) c on a.ref_cvid=c.cv_id ");
		//sql.append(" left join company_dept_title d on a.title_id=d.id ");
		sql.append(" left join (select t1.cv_id,min(t2.name) titleName from company_cv t1,company_dept_title t2 where t1.title_id=t2.id group by t1.cv_id) d on a.ref_cvid=d.cv_id ");
		sql.append(" left join cv_salary e on a.ref_cvid=e.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.ref_cvid=f.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.ref_cvid=h.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.ref_cvid=i.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.ref_cvid=j.cv_id ");		
		sql.append(" ) comcv ");
		List values = new ArrayList();
		
		sql.append(" where comcv.projectId=? ");
		values.add(query.getProjectId());
		if(isNotEmpty(query.getCompanyId())) {
 		    sql.append(" and comcv.companyId=? ");
		    values.add(query.getCompanyId());
		}
		
		//有cc
//		if ("1".equals(query.getHasCc())){
//			sql.append(" and exists( select 1 from ( select a.ref_cvid from company_cc a where exists (select 1 from search_project_company b where a.company_id=b.company_id and b.project_id=? )) bb where comcv.cvId=bb.ref_cvid ) ");
//		    values.add(query.getProjectId());
//		}
//		if ("1".equals(query.getHasCvlv())){
//			sql.append(" and exists (select 1 from cv a where ( exists (select 1 from cv_education a1 where a.id=a1.cv_id) or  exists (select 1 from cv_certificate a2 where a.id=a2.cv_id) or  exists (select 1 from cv_trainning a3 where a.id=a3.cv_id) or  exists (select 1 from cv_work a4 where a.id=a4.cv_id)) and comcv.cvId=a.id)  ");		    
//		}
		
		if(isNotEmpty(query.getCvName())) {
			sql.append(" and comcv.cvName like ? ");
			values.add("%"+query.getCvName()+"%");
		}
		System.out.println(sql.toString());
		if(isNotEmpty(query.getPhone())) {
			sql.append(" and ( comcv.phone like ?  or  comcv.mobile like ? ) ");
			values.add("%"+query.getPhone()+"%");
			values.add("%"+query.getPhone()+"%");
		}
		if(isNotEmpty(query.getEmail())) {
			sql.append(" and comcv.email like ? ");
			values.add("%"+query.getEmail()+"%");
		}
		
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber()); 
	}
	public Page findPage(CompanyCcQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		    Class[] os={CompanyCcQuery.class};
    		Class property= org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
        if (property!=null){
        	 if ("java.lang.String".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(),query.getQuery());
	    			
	    		}
	    		else if ("java.lang.Integer".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));
	    			
	    		}
	    		else if ("java.lang.Double".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
	    		}
	    		else if ("java.util.Date".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
	    		}
        }	 
        //生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
        StringBuilder sql2 = new StringBuilder("select t from CompanyCc t where 1=1 ");
        
        if(isNotEmpty(query.getProjectId())) {
           sql2.append(" and t.companyId in (select s.companySimple.id from SearchProjectCompany s where s.projectId in :projectId ) ");
	    }
          if(isNotEmpty(query.getId())) {
            sql2.append(" and  t.id = :id ");
        }
        if(isNotEmpty(query.getCallDtBegin())) {
            sql2.append(" and  t.callDt >= :callDtBegin ");
        }
        if(isNotEmpty(query.getCallDtEnd())) {
            sql2.append(" and  t.callDt <= :callDtEnd ");
        }
        if(isNotEmpty(query.getAccount())) {
            sql2.append(" and  t.account like :account ");
            query.setAccount("%"+query.getAccount()+"%"); 

        }
          if(isNotEmpty(query.getCompanyId())) {
            sql2.append(" and  t.companyId = :companyId ");
        }
          if(isNotEmpty(query.getDeptId())) {
            sql2.append(" and  t.deptId = :deptId ");
        }
          if(isNotEmpty(query.getTitleId())) {
            sql2.append(" and  t.titleId = :titleId ");
        }
        if(isNotEmpty(query.getTitleRemark())) {
            sql2.append(" and  t.titleRemark like :titleRemark ");
            query.setTitleRemark("%"+query.getTitleRemark()+"%"); 

        }
        if(isNotEmpty(query.getCaller())) {
            sql2.append(" and  t.caller like :caller ");
            query.setCaller("%"+query.getCaller()+"%"); 

        }
        if(isNotEmpty(query.getCallno())) {
            sql2.append(" and  t.callno like :callno ");
            query.setCallno("%"+query.getCallno()+"%"); 

        }
          if(isNotEmpty(query.getCallStatus())) {
            sql2.append(" and  t.callStatus = :callStatus ");
        }
          if(isNotEmpty(query.getCcResult())) {
            sql2.append(" and  t.ccResult = :ccResult ");
        }
          if(isNotEmpty(query.getRefCvid())) {
            sql2.append(" and  t.refCvid = :refCvid ");
        }
        if(isNotEmpty(query.getComment())) {
            sql2.append(" and  t.comment like :comment ");
            query.setComment("%"+query.getComment()+"%"); 

        }
        if(isNotEmpty(query.getCreator())) {
            sql2.append(" and  t.creator like :creator ");
            query.setCreator("%"+query.getCreator()+"%"); 

        }
        if(isNotEmpty(query.getCreateDtBegin())) {
            sql2.append(" and  t.createDt >= :createDtBegin ");
        }
        if(isNotEmpty(query.getCreateDtEnd())) {
            sql2.append(" and  t.createDt <= :createDtEnd ");
        }
        if(isNotEmpty(query.getModifier())) {
            sql2.append(" and  t.modifier like :modifier ");
            query.setModifier("%"+query.getModifier()+"%"); 

        }
        if(isNotEmpty(query.getModifiedDtBegin())) {
            sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
        }
        if(isNotEmpty(query.getModifiedDtEnd())) {
            sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
        }
        if(isNotEmpty(query.getSortColumns())) {
            sql2.append(" order by :sortColumns ");
        }	
        
		return pageQuery(sql2.toString(),query);
	}

	public Page findCcPage(CompanyCcQuery query) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		//sql.append(" select concat(ifnull(a.ref_cvid,0),ifnull(a.company_id,0)) id,a.project_id projectId,a.company_id companyId,a.call_dt callDt,'' cvSearchStatus,b1.name companyName,a.ref_cvid cvId,b.cv_name cvName,f.mobile,h.phone,c.deptName deptName,d.titleName titleName,e.current_salary currentSalary,i.email,j.tag,b.sex,b.function,b.level,b.degree,b.work_year workYear,b.expect_location expectLocation from (select project_id,company_id,dept_id,title_id,ref_cvid,max(call_dt) call_dt from company_cc  group by company_id,dept_id,title_id,project_id,ref_cvid) a ");
		sql.append(" select a.project_id projectId,a.id,a.call_dt callDt,a.account,a.company_id companyId,b1.name companyName,a.dept_id deptId,c.name deptName,a.title_id titleId,d.name titleName,a.title_remark titleRemark,a.caller,a.callno,a.call_status callStatus,a.cc_result ccResult,a.ref_cvid refCvid,b.cv_name cvName,a.comment,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from  company_cc a ");
		sql.append(" left join  cv b on a.ref_cvid=b.id ");
		sql.append(" left join  company b1 on a.company_id=b1.id ");
		//sql.append(" left join (select t1.cv_id,min(t2.name) deptName from company_cv t1,company_dept t2 where t1.dept_id=t2.id group by t1.cv_id) c on a.ref_cvid=c.cv_id ");
		sql.append(" left join company_dept c on a.dept_id=c.id ");
		sql.append(" left join company_dept_title d on a.title_id=d.id ");		
		sql.append(" order by a.create_dt desc ");
		sql.append(" ) comcc ");
		List values = new ArrayList();
		
		sql.append(" where comcc.projectId=? ");
		values.add(query.getProjectId());
		if(isNotEmpty(query.getCompanyId())) {
 		    sql.append(" and comcc.companyId=? ");
		    values.add(query.getCompanyId());
		}
		
		if(isNotEmpty(query.getCompanyName())) {
			sql.append(" and comcc.cvName like ? ");
			values.add("%"+query.getCvName()+"%");
		}
		
		if(isNotEmpty(query.getCvName())) {
			sql.append(" and comcc.cvName like ? ");
			values.add("%"+query.getCvName()+"%");
		}
		//System.out.println(sql.toString());

		
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber()); 
	}
	
	
	

}
